using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using NHibernate;
using NHibernate.Linq;
using NHibernate.Metadata;
using NHibernate.Persister.Entity;
using NHibernate.Util;
using Spring.Data.NHibernate.Generic;
using NHibernate.Criterion;

namespace Common.Dao
{
    /// <summary>
    /// Extended Hibernate Template class to provide more functionality base on Spring.NET framework
    /// Hibernate Template
    /// </summary>
    public class HibernateTemplateEx : HibernateTemplate
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="HibernateTemplateEx"/> class.
        /// </summary>
        /// <param name="sessionFactory">
        /// The session factory.
        /// </param>
        /// <param name="allowCreate">
        /// Is allow creating a new session or not.
        /// </param>
        public HibernateTemplateEx(ISessionFactory sessionFactory, bool allowCreate)
            : base(sessionFactory, allowCreate)
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="HibernateTemplateEx"/> class.
        /// </summary>
        /// <param name="sessionFactory">
        /// The session factory.
        /// </param>
        public HibernateTemplateEx(ISessionFactory sessionFactory)
            : base(sessionFactory)
        {
        }

        /// <summary>
        /// Delete an entity by its key
        /// </summary>
        /// <typeparam name="T">Entity type</typeparam>
        /// <param name="entities">Entities to delete</param>
        public void DeleteAll<T>(IList<T> entities)
        {
            foreach (var entity in entities)
            {
                this.Delete(entity);
            }
        }

        /// <summary>
        /// Delete an entity by its key
        /// </summary>
        /// <typeparam name="TK">The key type</typeparam>
        /// <param name="entityName">The entity name</param>
        /// <param name="key">The key value</param>
        /// <returns>Number of affected record, 0 if not found</returns>
        public int DeleteByKey<TK>(string entityName, TK key)
        {
            var deleteHql = string.Concat("delete ", entityName, " where id=?");
            return this.BulkUpdate(deleteHql, new object[]{key});
        }

        /// <summary>
        /// Verify the entity has natural id defined or not, also check the number parameters is sufficient or not
        /// </summary>
        /// <param name="session">The Hibernate session</param>
        /// <param name="entityName">The entity name to check</param>
        /// <param name="paraLength">The number of paramenters</param>
        /// <returns></returns>
        protected IClassMetadata VerifyNaturalId(ISession session, string entityName, int paraLength)
        {
            var metaData = session.SessionFactory.GetClassMetadata(entityName);
            if (!metaData.HasNaturalIdentifier)
            {
                throw new ArgumentException("Entity doesn't have natural id");
            }

            var propIndices = metaData.NaturalIdentifierProperties;
            if ((paraLength>=1) && (propIndices.Length != paraLength))
            {                
                throw new ArgumentException("Number of parameters doesn't match with number of natural properties");
            }

            return metaData;
        }

        /// <summary>
        /// Delete entity by its natural id, passing array of parameter paras 
        /// follow the natural id properties-columns mapping order.
        /// </summary>
        /// <param name="entityName">The entity name</param>
        /// <param name="paras">The parameter values</param>
        /// <returns>Number of affected record, 0 if not found</returns>
        public int DeleteByNaturalId(string entityName, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var metaData = this.VerifyNaturalId(session, entityName, paras.Length);
                        var propIndices = metaData.NaturalIdentifierProperties;
                        var propNames = metaData.PropertyNames;
                        var hqlbuilder = new StringBuilder(50);
                        hqlbuilder.Append("delete ").Append(entityName).Append(" where ");
                        for (var i = 0; i < propIndices.Length; i++)
                        {
                            if (i > 0)
                            {
                                hqlbuilder.Append(propNames[propIndices[i]]).Append("=?");
                            }
                        }

                        var queryObject = session.CreateQuery(hqlbuilder.ToString());
                        this.PrepareQuery(queryObject);
                        if (paras != null)
                        {
                            for (var i = 0; i < paras.Length; i++)
                            {
                                queryObject.SetParameter(i, paras[i]);
                            }
                        }

                        return queryObject.ExecuteUpdate();
                    });
        }

        /// <summary>
        /// Delete entity by its natural id, passing array of parameter paras 
        /// follow the natural id properties-columns mapping order.
        /// </summary>
        /// <param name="entityName">The entity name</param>
        /// <param name="valueObject">The parameter value object</param>
        /// <returns>Number of affected record, 0 if not found</returns>
        public int DeleteByNaturalIdAndValueObject(string entityName, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var metaData = this.VerifyNaturalId(session, entityName, 0);
                        var propIndices = metaData.NaturalIdentifierProperties;
                        var propNames = metaData.PropertyNames;
                        var hqlbuilder = new StringBuilder(50);
                        hqlbuilder.Append("delete ").Append(entityName).Append(" where ");
                        for (var i = 0; i < propIndices.Length; i++)
                        {
                            if (i > 0)
                            {
                                var propName = propNames[propIndices[i]];
                                hqlbuilder.Append(propName).Append("=@").Append(propName);
                            }
                        }

                        var queryObject = session.CreateQuery(hqlbuilder.ToString());
                        this.PrepareQuery(queryObject);
                        if (valueObject != null)
                        {
                            queryObject.SetProperties(valueObject);
                        }

                        return queryObject.ExecuteUpdate();
                    });
        }

        /// <summary>
        /// Get entity by its natural id, passing array of parameter paras 
        /// follow the natural id properties-columns mapping order.
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="entityName">
        /// The entity name.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <returns>
        /// The matched entity.
        /// </returns>
        public T GetByNaturalId<T>(string entityName, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var metaData = this.VerifyNaturalId(session, entityName, paras.Length);
                        var propIndices = metaData.NaturalIdentifierProperties;
                        var propNames = metaData.PropertyNames;
                        var criteria = session.CreateCriteria(entityName);                    
                        var restriction = Restrictions.NaturalId();
                        for (var i = 0; i < propIndices.Length; i++)
                        {
                            restriction.Set(propNames[propIndices[i]], paras[i]);
                        }

                        return criteria.UniqueResult<T>();
                    });
        }

        /// <summary>
        /// Get entity by its natural id, passing array of parameter paras 
        /// follow the natural id properties-columns mapping order.
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="entityName">
        /// The entity name.
        /// </param>
        /// <param name="valueObject">
        /// The query parameter value object.
        /// </param>
        /// <returns>
        /// The matched entity.
        /// </returns>
        public T GetByNaturalIdAndValueObject<T>(string entityName, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var metaData = this.VerifyNaturalId(session, entityName, 0);
                        var propIndices = metaData.NaturalIdentifierProperties;
                        var propNames = metaData.PropertyNames;
                        var criteria = session.CreateCriteria(entityName);
                        var restriction = Restrictions.NaturalId();
                        if (valueObject != null)
                        {
                            var typeOfObj = valueObject.GetType();
                            foreach (var t in propIndices)
                            {
                                var propName = propNames[t];
                                try
                                {
                                    var getter = ReflectHelper.GetGetter(typeOfObj, propName, "property");
                                    var val = getter.Get(valueObject);
                                    restriction.Set(propName, val);
                                }
                                catch (PropertyNotFoundException)
                                {
                                }
                            }
                        }

                        return criteria.UniqueResult<T>();
                    });
        }

        /// <summary>
        /// Load all entity specified by entity name.
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="entityName">
        /// Entity name to load.
        /// </param>
        /// <returns>
        /// List of entity objects.
        /// </returns>
        public IList<T> LoadAll<T>(string entityName)
        {
            return this.Execute(
                session =>
                    {
                        var criteria = session.CreateCriteria(entityName);
                        this.PrepareCriteria(criteria);
                        return criteria.List<T>();
                    });
        }

        /// <summary>
        /// Find matched entities by providing a LinqQuery delegate and its parameter paras.
        /// </summary>
        /// <param name="linqQuery">
        /// The <see cref="LinqQuery{T,TRet}"/> delegate.
        /// </param>
        /// <param name="firstResult">
        /// The first result (zero based).
        /// </param>
        /// <param name="maxResults">
        /// The max results, negative for all results.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <typeparam name="T">
        /// The orginal queryable element type from Linq 
        /// for Hibernate extension.
        /// </typeparam>
        /// <typeparam name="TRet">
        /// The returned list element type.
        /// </typeparam>
        /// <returns>
        /// The query return list.
        /// </returns>
        public IList<TRet> Find<T, TRet>(LinqQuery<T, TRet> linqQuery, int firstResult, int maxResults, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var context = session.Linq<T>();
                        return linqQuery(context, paras).Skip(firstResult).Take(maxResults).ToList();
                    });
        }

        public IList<TRet> Find<T, TRet, TKey>(LinqQuery<T, TRet> linqQuery, int firstResult, int maxResults, 
                                               Func<TRet, TKey> orderBy,bool isAscending, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var context = session.Linq<T>();
                        var query = linqQuery(context, paras); 
                        if (isAscending)
                        {
                            return query.OrderBy(orderBy).Skip(firstResult).Take(maxResults).ToList();
                        } 
                    
                        return query.OrderByDescending(orderBy).Skip(firstResult).Take(maxResults).ToList();
                    });
        }

        /// <summary>
        /// Execute an HQL query, binding paras to "?" parameters in the query
        /// string, only get the first entity and return null if there is no record
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <param name="queryString">
        /// a query expressed in Hibernate's query language.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <returns>
        /// The first matched entity and return null if there is no record.
        /// </returns>
        public T FindOne<T>(string queryString, object[] paras)
        {
            var list = this.Find<T>(queryString, 0, 1, paras);
            return list.Count > 0 ? list[0] : default(T);
        }

        /// <summary>
        /// Execute an HQL query, binding parameters in the query
        /// string to a value object, only get the first entity and return null if there is no record
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <param name="queryString">
        /// a query expressed in Hibernate's query language.
        /// </param>
        /// <param name="valueObject">
        /// The query parameter value object.
        /// </param>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <returns>
        /// The first matched entity and return null if there is no record.
        /// </returns>
        public T FindOneByValueObject<T>(string queryString, object valueObject)
        {
            var list = this.FindByValueObject<T>(queryString, 0, 1, valueObject);
            return list.Count > 0 ? list[0] : default(T);
        }

        /// <summary>
        /// Execute an HQL query, binding paras to "?" parameters in the query
        /// string, only get the first entity and return null if there is no record
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care)
        /// </summary>
        /// <param name="queryString">
        /// A query expressed in Hibernate's query language.
        /// </param>
        /// <param name="lockMode">
        /// The query parameter values
        /// </param>
        /// <param name="lockAlias">
        /// The lock alias.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <returns>
        /// The first matched entity and return null if there is no record.
        /// </returns>
        public T FindOne<T>(string queryString, LockMode lockMode, string lockAlias, object[] paras)
        {
            var list = this.Find<T>(queryString, lockMode, lockAlias, 0, 1, paras);
            return list.Count > 0 ? list[0] : default(T);
        }

        /// <summary>
        /// Execute an HQL query, binding parameters in the query
        /// string to a value object, only get the first entity and return null if there is no record
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care)
        /// </summary>
        /// <param name="queryString">
        /// A query expressed in Hibernate's query language.
        /// </param>
        /// <param name="lockMode">
        /// The query parameter values
        /// </param>
        /// <param name="lockAlias">
        /// The lock alias.
        /// </param>
        /// <param name="valueObject">
        /// The query parameter value object.
        /// </param>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <returns>
        /// The first matched entity and return null if there is no record.
        /// </returns>
        public T FindOneByValueObject<T>(string queryString, LockMode lockMode, string lockAlias, object valueObject)
        {
            var list = this.FindByValueObject<T>(queryString, lockMode, lockAlias, 0, 1, valueObject);
            return list.Count > 0 ? list[0] : default(T);
        }

        /// <summary>
        /// Execute a named HQL query, binding paras to "?" parameters in the query
        /// string, only get the first entity and return null if there is no record
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <param name="queryName">
        /// A named query expressed in Hibernate's query language, is defined in a mapping file.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <typeparam name="T">
        /// The specific type
        /// </typeparam>
        /// <returns>
        /// The first matched entity and return null if there is no record
        /// </returns>
        public T FindOneByName<T>(string queryName, object[] paras)
        {
            var list = this.FindByNamedQuery<T>(queryName, 0, 1, paras);
            return list.Count > 0 ? list[0] : default(T);
        }

        /// <summary>
        /// Execute a named HQL query, binding parameters in the query
        /// string to a value object, only get the first entity and return null if there is no record
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <param name="queryName">
        /// A named query expressed in Hibernate's query language, is defined in a mapping file.
        /// </param>
        /// <param name="valueObject">
        /// The query parameter value object.
        /// </param>
        /// <typeparam name="T">
        /// The specific type
        /// </typeparam>
        /// <returns>
        /// The first matched entity and return null if there is no record
        /// </returns>
        public T FindOneByNameAndValueObject<T>(string queryName, object valueObject)
        {
            var list = this.FindByNamedQueryAndValueObject<T>(queryName, 0, 1, valueObject);
            return list.Count > 0 ? list[0] : default(T);
        }

        /// <summary>
        /// Execute an HQL query, binding paras to "?" parameters in the query
        /// string to find all matched entities
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <param name="queryString">
        /// A query expressed in Hibernate's query language.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <returns>
        /// A list containing the results of the query execution.
        /// </returns>
        public IList<T> FindAll<T>(string queryString, object[] paras)
        {
            return this.Find<T>(queryString, -1, -1, paras);
        }        

        /// <summary>
        /// Execute an HQL query, binding paras to "?" parameters in the query
        /// string, limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="queryString">
        /// The query string.
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <returns>
        /// a list containing the results of the query execution
        /// </returns>
        public IList<T> Find<T>(string queryString, int firstResult, int maxResults, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery(queryString);
                        return this.Query<T>(queryObject, firstResult, maxResults, paras);
                    });
        }

        /// <summary>
        /// Execute an HQL query, binding paras to "?" parameters in the query
        /// string and return number of matched entities
        /// </summary>
        /// <param name="queryString">
        /// The query string.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <returns>
        /// Number of matched entity
        /// </returns>
        public int GetCount(string queryString, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery("select count(*) " + queryString);
                        this.PrepareQuery(queryObject);
                        if (paras != null)
                        {
                            for (var i = 0; i < paras.Length; i++)
                            {
                                queryObject.SetParameter(i, paras[i]);
                            }
                        }

                        return queryObject.UniqueResult<int>();
                    });
        }

        /// <summary>
        /// Execute an HQL query, binding parameters in the query
        /// string to a value object and return number of matched entities
        /// </summary>
        /// <param name="queryString">
        /// The query string.
        /// </param>
        /// <param name="valueObject">
        /// The query parameter values.
        /// </param>
        /// <returns>
        /// Number of matched entity
        /// </returns>
        public int GetCountByValueObject(string queryString, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery("select count(*) " + queryString);
                        this.PrepareQuery(queryObject);
                        if (valueObject != null)
                        {
                            queryObject.SetProperties(valueObject);
                        }

                        return queryObject.UniqueResult<int>();
                    });
        }

        /// <summary>
        /// Execute an HQL query, binding named parameters in the query
        /// string to value object properties, limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="queryString">
        /// The query string.
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="valueObject">
        /// The query parameter value object.
        /// </param>
        /// <returns>
        /// a list containing the results of the query execution.
        /// </returns>
        public IList<T> FindByValueObject<T>(string queryString, int firstResult, int maxResults, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery(queryString);
                        this.PrepareQuery(queryObject);
                        if (valueObject != null)
                        {
                            queryObject.SetProperties(valueObject);
                        }

                        if (firstResult >= 0)
                        {
                            queryObject.SetFirstResult(firstResult);
                        }

                        if (maxResults > 0)
                        {
                            queryObject.SetMaxResults(maxResults);
                        }

                        return queryObject.List<T>();
                    });
        }        

        /// <summary>
        /// Execute a named HQL query, binding paras to "?" parameters in the query
        /// string.
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <param name="queryName">
        /// A named query expressed in Hibernate's query language, is defined in a mapping file.
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <returns>
        /// The first matched entity and return null if there is no record.
        /// </returns>
        public IList<T> FindByNamedQuery<T>(string queryName, int firstResult, int maxResults, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.GetNamedQuery(queryName);
                        return this.Query<T>(queryObject, firstResult, maxResults, paras);
                    });
        }

        /// <summary>
        /// Execute a named HQL query, binding named parameters in the query
        /// string to a value object properties.
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <param name="queryName">
        /// A named query expressed in Hibernate's query language, is defined in a mapping file.
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="valueObject">
        /// The query parameter value object
        /// </param>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <returns>
        /// The first matched entity and return null if there is no record.
        /// </returns>
        public IList<T> FindByNamedQueryAndValueObject<T>(string queryName, int firstResult, int maxResults, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.GetNamedQuery(queryName);
                        return this.QueryByValueObject<T>(queryObject, firstResult, maxResults, valueObject);
                    });
        }

        /// <summary>
        /// Execute an SQL query, binding paras to "?" parameters in the query
        /// string, limit the result list by firstResult and maxResult.
        /// Map the result element to entity type.
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="sql">
        /// The query string.
        /// </param>
        /// <param name="entityName">
        /// The entity name
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <returns>
        /// A list containing the results of the query execution
        /// </returns>
        public IList<T> FindSql<T>(string sql, string entityName, int firstResult, int maxResults, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateSQLQuery(sql);
                        queryObject.AddEntity(entityName);
                        return this.Query<T>(queryObject, firstResult, maxResults, paras);
                    });
        }

        /// <summary>
        /// Execute an SQL query, binding arameters in the query
        /// string to a value object properties, limit the result list by firstResult and maxResult.
        /// Map the result element to entity type.
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="sql">
        /// The query string.
        /// </param>
        /// <param name="entityName">
        /// The entity name
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="valueObject">
        /// The query parameter value object.
        /// </param>
        /// <returns>
        /// A list containing the results of the query execution
        /// </returns>
        public IList<T> FindSql<T>(string sql, string entityName, int firstResult, int maxResults, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateSQLQuery(sql);
                        queryObject.AddEntity(entityName);
                        return this.QueryByValueObject<T>(queryObject, firstResult, maxResults, valueObject);
                    });
        }

        /// <summary>
        /// Execute an SQL query, binding paras to "?" parameters in the query
        /// string, limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="sql">
        /// The query string.
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <returns>
        /// a list containing the results of the query execution
        /// </returns>
        public IList<T> FindSql<T>(string sql, int firstResult, int maxResults, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var type = typeof(T);
                        var queryObject = session.CreateSQLQuery(sql);
                        if (!(typeof(object[]).IsAssignableFrom(type)))
                        {
                            queryObject.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(type));
                        }

                        return this.Query<T>(queryObject, firstResult, maxResults, paras);
                    });
        }

        /// <summary>
        /// Execute a named HQL query, binding paras to "?" parameters in the query
        /// string
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="sqlName">
        /// A named query expressed in Hibernate's query language, is defined in a mapping file.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <returns>
        /// a list containing the results of the query execution
        /// </returns>
        public IList<T> FindSqlByNamedQuery<T>(string sqlName, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var type = typeof(T);                    
                        var queryObject = session.GetNamedQuery(sqlName);
                        if (!(typeof(object[]).IsAssignableFrom(type)))
                        {
                            queryObject.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(type));
                        }
                        return this.Query<T>(queryObject, -1, -1, paras);
                    });
        }

        /// <summary>
        /// Execute a named HQL query, binding paras to "?" parameters in the query
        /// string, limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="sqlName">
        /// A named query expressed in Hibernate's query language, is defined in a mapping file.
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <returns>
        /// a list containing the results of the query execution
        /// </returns>
        public IList<T> FindSqlByNamedQueryAndNamedParams<T>(string sqlName, int firstResult, int maxResults, IDictionary<string, object> paras)
        {
            return this.Execute(
                session =>
                    {
                        var type = typeof(T);
                        var queryObject = session.GetNamedQuery(sqlName);
                        if (!(typeof(object[]).IsAssignableFrom(type)))
                        {
                            queryObject.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(type));
                        }
                        return this.QueryByNamedParams<T>(queryObject, firstResult, maxResults, paras);
                    });
        }

        /// <summary>
        /// Execute an SQL query, binding parameters in the query
        /// string to a value object parameters, limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="sql">
        /// The query string.
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="valueObject">
        /// The query parameter value object.
        /// </param>
        /// <returns>
        /// A list containing the results of the query execution
        /// </returns>
        public IList<T> FindSqlByValueObject<T>(string sql, int firstResult, int maxResults, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var type = typeof(T);                    
                        var queryObject = session.CreateSQLQuery(sql);
                        if (!(typeof(object[]).IsAssignableFrom(type)))
                        {
                            queryObject.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(type));
                        }
                        return this.QueryByValueObject<T>(queryObject, firstResult, maxResults, valueObject);
                    });
        }

        /// <summary>
        /// Execute a named HQL query, binding parameters in the query
        /// string to a value object parameters, limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="sqlName">
        /// A named query expressed in Hibernate's query language, is defined in a mapping file.
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="valueObject">
        /// The query parameter value object.
        /// </param>
        /// <returns>
        /// A list containing the results of the query execution
        /// </returns>
        public IList<T> FindSqlByNamedQueryAndValueObject<T>(string sqlName, int firstResult, int maxResults, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var type = typeof(T);
                        var queryObject = session.GetNamedQuery(sqlName);
                        if (!(typeof(object[]).IsAssignableFrom(type)))
                        {
                            queryObject.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(type));
                        }
                        return this.QueryByValueObject<T>(queryObject, firstResult, maxResults, valueObject);
                    });
        }

        /// <summary>
        /// Execute an HQL query, binding paras to "?" parameters in the query
        /// string, limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care)
        /// </summary>
        /// <typeparam name="T">
        /// The specific return type
        /// </typeparam>
        /// <param name="queryString">
        /// A query expressed in Hibernate's query language
        /// </param>
        /// <param name="lockMode">
        /// The lock mode.
        /// </param>
        /// <param name="lockAlias">
        /// The lock alias.
        /// </param>
        /// <param name="paras">
        /// The parameter values
        /// </param>
        /// <param name="firstResult">
        /// The index of the first result object to be retrieved (numbered from 0)
        /// </param>
        /// <param name="maxResults">
        /// The maximum number of result objects to retrieve (or &lt;=0 for no limit)
        /// </param>
        /// <returns>
        /// A containing the results of the query execution
        /// </returns>
        public IList<T> Find<T>(
            string queryString, LockMode lockMode, string lockAlias, int firstResult, int maxResults, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery(queryString);
                        queryObject.SetLockMode(lockAlias, lockMode);
                        return this.Query<T>(queryObject, firstResult, maxResults, paras);
                    });
        }

        /// <summary>
        /// Execute an HQL query, binding parameters in the query
        /// string to a value object parameters, limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care)
        /// </summary>
        /// <typeparam name="T">
        /// The specific return type
        /// </typeparam>
        /// <param name="queryString">
        /// A query expressed in Hibernate's query language
        /// </param>
        /// <param name="lockMode">
        /// The lock mode.
        /// </param>
        /// <param name="lockAlias">
        /// The lock alias.
        /// </param>
        /// <param name="firstResult">
        /// The index of the first result object to be retrieved (numbered from 0)
        /// </param>
        /// <param name="maxResults">
        /// The maximum number of result objects to retrieve (or &lt;=0 for no limit)
        /// </param>
        /// <param name="valueObject">
        /// The query parameter value object.
        /// </param>
        /// <returns>
        /// A containing the results of the query execution
        /// </returns>
        public IList<T> FindByValueObject<T>(
            string queryString, LockMode lockMode, string lockAlias, int firstResult, int maxResults, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery(queryString);
                        queryObject.SetLockMode(lockAlias, lockMode);
                        return this.QueryByValueObject<T>(queryObject, firstResult, maxResults, valueObject);
                    });
        }

        /// <summary>
        /// Execute a query with indexed parameter values
        /// </summary>
        /// <param name="query">The query object to execute</param>
        /// <param name="paras">The query parameter values</param>
        /// <returns>Number of records affected</returns>
        protected int QueryUpdate(IQuery query, object[] paras)
        {
            this.PrepareQuery(query);
            if (paras != null)
            {
                for (var i = 0; i < paras.Length; i++)
                {
                    query.SetParameter(i, paras[i]);
                }
            }

            return query.ExecuteUpdate();
        }

        /// <summary>
        /// Execute a query with indexed parameter values
        /// </summary>
        /// <param name="query">The query object to execute</param>
        /// <param name="valueObject">The query parameter value object</param>
        /// <returns>Number of records affected</returns>
        protected int QueryUpdateByValueObject(IQuery query, object valueObject)
        {
            this.PrepareQuery(query);
            if (valueObject != null)
            {
                query.SetProperties(valueObject);
            }

            return query.ExecuteUpdate();
        }

        /// <summary>
        /// Execute a DML style HQL query, binding paras to "?" parameters in the query
        /// string.
        /// </summary>
        /// <param name="queryString">
        /// A query expressed in Hibernate's query language
        /// </param>
        /// <param name="lockMode">
        /// The lock mode.
        /// </param>
        /// <param name="lockAlias">
        /// The lock alias.
        /// </param>
        /// <param name="paras">
        /// The parameter values
        /// </param>
        /// <returns>
        /// Number of affected entities
        /// </returns>
        public int BulkUpdate(string queryString, LockMode lockMode, string lockAlias, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery(queryString);
                        queryObject.SetLockMode(lockAlias, lockMode);
                        return this.QueryUpdate(queryObject, paras);
                    });
        }

        /// <summary>
        /// Execute a DML style HQL query, binding  parameters in the query
        /// string to a value object parameters.
        /// </summary>
        /// <param name="queryString">
        /// A query expressed in Hibernate's query language
        /// </param>
        /// <param name="lockMode">
        /// The lock mode.
        /// </param>
        /// <param name="lockAlias">
        /// The lock alias.
        /// </param>
        /// <param name="valueObject">
        /// The parameter value object.
        /// </param>
        /// <returns>
        /// Number of affected entities.
        /// </returns>
        public int BulkUpdateByValueObject(string queryString, LockMode lockMode, string lockAlias, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery(queryString);
                        queryObject.SetLockMode(lockAlias, lockMode);
                        return this.QueryUpdateByValueObject(queryObject, valueObject);
                    });
        }

        /// <summary>
        /// Execute a DML style HQL query, binding paras to "?" parameters in the query
        /// string.
        /// </summary>
        /// <param name="queryString">
        /// A query expressed in Hibernate's query language
        /// </param>
        /// <param name="paras">
        /// The parameter values
        /// </param>
        /// <returns>
        /// Number of affected entities
        /// </returns>
        public int BulkUpdate(string queryString, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery(queryString);
                        return this.QueryUpdate(queryObject, paras);
                    });
        }

        /// <summary>
        /// Execute a DML style HQL query, binding parameters in the query
        /// string to a value object.
        /// </summary>
        /// <param name="queryString">
        /// A query expressed in Hibernate's query language.
        /// </param>
        /// <param name="valueObject">
        /// The parameter value object.
        /// </param>
        /// <returns>
        /// Number of affected entities.
        /// </returns>
        public int BulkUpdateByValueObject(string queryString, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery(queryString);
                        return this.QueryUpdateByValueObject(queryObject, valueObject);
                    });
        }

        /// <summary>
        /// Execute a named DML style HQL query, binding paras to "?" parameters in the query
        /// string
        /// </summary>
        /// <param name="queryName">
        /// A named query expressed in Hibernate's query language, is defined in a mapping file.
        /// </param>
        /// <param name="paras">
        /// The parameter values
        /// </param>
        /// <returns>
        /// Number of affected entities
        /// </returns>
        public int BulkUpdateByName(string queryName, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.GetNamedQuery(queryName);
                        // queryObject.
                        return this.QueryUpdate(queryObject, paras);
                    });
        }

        /// <summary>
        /// Execute a named DML style HQL query, binding  parameters in the query
        /// string to a value object properties
        /// </summary>
        /// <param name="queryName">
        /// A named query expressed in Hibernate's query language, is defined in a mapping file.
        /// </param>
        /// <param name="valueObject">
        /// The parameter values
        /// </param>
        /// <returns>
        /// Number of affected entities
        /// </returns>
        public int BulkUpdateByNameAndValueObject(string queryName, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.GetNamedQuery(queryName);
                        return this.QueryUpdateByValueObject(queryObject, valueObject);
                    });
        }

        /// <summary>
        /// Execute a DML style SQL query, binding paras to "?" parameters in the query
        /// string
        /// </summary>
        /// <param name="sql">
        /// A sql query
        /// </param>
        /// <param name="paras">
        /// The parameter values
        /// </param>
        /// <returns>
        /// Number of affected records
        /// </returns>
        public int BulkUpdateSql(string sql, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateSQLQuery(sql);
                        this.PrepareQuery(queryObject);
                        return this.QueryUpdate(queryObject, paras);
                    });
        }

        /// <summary>
        /// Execute a DML style SQL query, binding paras to "?" parameters in the query
        /// string
        /// </summary>
        /// <param name="sql">
        /// A sql query.
        /// </param>
        /// <param name="valueObject">
        /// The parameter value object.
        /// </param>
        /// <returns>
        /// Number of affected records.
        /// </returns>
        public int BulkUpdateSqlAndValueObject(string sql, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateSQLQuery(sql);
                        this.PrepareQuery(queryObject);
                        return this.QueryUpdateByValueObject(queryObject, valueObject);
                    });
        }

        /// <summary>
        /// Execute a query and return an enumerable for the result with indexed parameter
        /// </summary>
        /// <typeparam name="T">The returned list element type</typeparam>
        /// <param name="query">The query object</param>
        /// <param name="firstResult">
        /// The index of the first result object to be retrieved (numbered from 0)
        /// </param>
        /// <param name="maxResults">
        /// The maximum number of result objects to retrieve (or &lt;=0 for no limit)
        /// </param>
        /// <param name="paras">The query paramenter values</param>
        /// <returns>The enumerable to iterate with the result</returns>
        protected IEnumerable<T> QueryIterate<T>(IQuery query, int firstResult, int maxResults, object[] paras)
        {
            this.PrepareQuery(query);
            if (paras != null)
            {
                for (var i = 0; i < paras.Length; i++)
                {
                    query.SetParameter(i, paras[i]);
                }
            }

            if (firstResult >= 0)
            {
                query.SetFirstResult(firstResult);
            }

            if (maxResults > 0)
            {
                query.SetMaxResults(maxResults);
            }

            return query.Enumerable<T>();
        }

        /// <summary>
        /// Execute a query and return an enumerable for the result with value object parameter
        /// </summary>
        /// <typeparam name="T">The returned list element type</typeparam>
        /// <param name="query">The query object</param>
        /// <param name="firstResult">
        /// The index of the first result object to be retrieved (numbered from 0)
        /// </param>
        /// <param name="maxResults">
        /// The maximum number of result objects to retrieve (or &lt;=0 for no limit)
        /// </param>
        /// <param name="valueObject">The query paramenter value object</param>
        /// <returns>The enumerable to iterate with the result</returns>
        protected IEnumerable<T> QueryIterateByValueObject<T>(IQuery query, int firstResult, int maxResults, object valueObject)
        {
            this.PrepareQuery(query);
            if (valueObject != null)
            {
                query.SetProperties(valueObject);
            }

            if (firstResult >= 0)
            {
                query.SetFirstResult(firstResult);
            }

            if (maxResults > 0)
            {
                query.SetMaxResults(maxResults);
            }

            return query.Enumerable<T>();
        }

        /// <summary>
        /// Execute an HQL query, binding paras to "?" parameters in the query
        /// string, limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// Then return an enumerable for one by interact with the element of the result list
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="queryString">
        /// The query string.
        /// </param>
        /// <param name="lockMode">
        /// The lock mode.
        /// </param>
        /// <param name="lockAlias">
        /// The lock alias.
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <returns>
        /// a list containing the results of the query execution
        /// </returns>
        public IEnumerable<T> Iterate<T>(
            string queryString, LockMode lockMode, string lockAlias, int firstResult, int maxResults, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery(queryString);
                        queryObject.SetLockMode(lockAlias, lockMode);
                        return this.QueryIterate<T>(queryObject, firstResult, maxResults, paras);
                    });
        }

        /// <summary>
        /// Execute an HQL query, binding parameters in the query
        /// string to a value object properties,
        /// limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// Then return an enumerable for one by interact with the element of the result list
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="queryString">
        /// The query string.
        /// </param>
        /// <param name="lockMode">
        /// The lock mode.
        /// </param>
        /// <param name="lockAlias">
        /// The lock alias.
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="valueObject">
        /// The query parameter value object.
        /// </param>
        /// <returns>
        /// A list containing the results of the query execution
        /// </returns>
        public IEnumerable<T> IterateByValueObject<T>(
            string queryString, LockMode lockMode, string lockAlias, int firstResult, int maxResults, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery(queryString);
                        queryObject.SetLockMode(lockAlias, lockMode);
                        return this.QueryIterateByValueObject<T>(queryObject, firstResult, maxResults, valueObject);
                    });
        }

        /// <summary>
        /// Execute an HQL query, binding parameters in the query
        /// string to a value object properties,
        /// limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// Then return an enumerable for one by interact with the element of the result list
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="queryString">
        /// The query string.
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="valueObject">
        /// The query parameter value object.
        /// </param>
        /// <returns>
        /// A list containing the results of the query execution
        /// </returns>
        public IEnumerable<T> IterateByValueObject<T>(
            string queryString, int firstResult, int maxResults, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery(queryString);
                        return this.QueryIterateByValueObject<T>(queryObject, firstResult, maxResults, valueObject);
                    });
        }

        /// <summary>
        /// Execute an HQL query, binding paras to "?" parameters in the query
        /// string, limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care).
        /// Then return an enumerable for one by interact with the element of the result list
        /// </summary>
        /// <typeparam name="T">
        /// The specific type.
        /// </typeparam>
        /// <param name="queryString">
        /// The query string.
        /// </param>
        /// <param name="firstResult">
        /// The first result.
        /// </param>
        /// <param name="maxResults">
        /// Number of expected return objects.
        /// </param>
        /// <param name="paras">
        /// The query parameter values.
        /// </param>
        /// <returns>
        /// a list containing the results of the query execution
        /// </returns>
        public IEnumerable<T> Iterate<T>(string queryString, int firstResult, int maxResults, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = session.CreateQuery(queryString);
                        return this.QueryIterate<T>(queryObject, firstResult, maxResults, paras);
                    });
        }

        /// <summary>
        /// Truncate related table of an entity, fallback to delete if the entity is mapped to many tables
        /// </summary>
        /// <param name="entityName">
        /// The entity name.
        /// </param>
        /// <returns>
        /// Number of affected records
        /// </returns>
        public int Truncate(string entityName)
        {
            return this.Execute(
                session =>
                    {
                        var metaData = session.SessionFactory.GetClassMetadata(entityName);
                        if (metaData is SingleTableEntityPersister)
                        {
                            var persister = (SingleTableEntityPersister)metaData;
                            var tableName = persister.TableName;
                            return session.CreateSQLQuery("truncate table " + tableName).ExecuteUpdate();
                        }

                        return session.CreateQuery("delete " + entityName).ExecuteUpdate();
                    });
        }

        /// <summary>
        /// Execute a <see cref="IQuery"/>, binding paras to "?" parameters in the query
        /// string, limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care)
        /// </summary>
        /// <typeparam name="T">
        /// The specific return type
        /// </typeparam>
        /// <param name="queryObject">
        /// Hibernate query object
        /// </param>
        /// <param name="paras">
        /// The parameter values
        /// </param>
        /// <param name="firstResult">
        /// The index of the first result object to be retrieved (numbered from 0)
        /// </param>
        /// <param name="maxResults">
        /// The maximum number of result objects to retrieve (or &lt;=0 for no limit)
        /// </param>
        /// <returns>
        /// A containing the results of the query execution
        /// </returns>
        protected IList<T> Query<T>(IQuery queryObject, int firstResult, int maxResults, object[] paras)
        {
            this.PrepareQuery(queryObject);
            if (paras != null)
            {
                for (var i = 0; i < paras.Length; i++)
                {
                    queryObject.SetParameter(i, paras[i]);
                }
            }

            if (firstResult >= 0)
            {
                queryObject.SetFirstResult(firstResult);
            }

            if (maxResults > 0)
            {
                queryObject.SetMaxResults(maxResults);
            }

            return queryObject.List<T>();
        }

        /// <summary>
        /// Execute query with named parameters
        /// </summary>
        /// <typeparam name="T">The type of return object element type</typeparam>
        /// <param name="queryObject">The query object</param>
        /// <param name="firstResult">The first result index</param>
        /// <param name="maxResults">The maxinum return number of records</param>
        /// <param name="paras">Named parameters as dictionary</param>
        /// <returns>The list of returned object</returns>
        protected IList<T> QueryByNamedParams<T>(IQuery queryObject, int firstResult, int maxResults, IDictionary<string,object> paras)
        {
            this.PrepareQuery(queryObject);
            foreach (var paramName in paras.Keys)
            { 
                queryObject.SetParameter(paramName, paras[paramName]);
            }

            if (firstResult >= 0)
            {
                queryObject.SetFirstResult(firstResult);
            }

            if (maxResults > 0)
            {
                queryObject.SetMaxResults(maxResults);
            }

            return queryObject.List<T>();
        }

        /// <summary>
        /// Execute a <see cref="IQuery"/>,
        /// binding named parameters in the query string with properties of a value object, 
        /// limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care)
        /// </summary>
        /// <typeparam name="T">
        /// The specific return type
        /// </typeparam>
        /// <param name="queryObject">
        /// Hibernate query object
        /// </param>
        /// <param name="valueObject">
        /// The parameter value object
        /// </param>
        /// <param name="firstResult">
        /// The index of the first result object to be retrieved (numbered from 0)
        /// </param>
        /// <param name="maxResults">
        /// The maximum number of result objects to retrieve (or &lt;=0 for no limit)
        /// </param>
        /// <returns>
        /// A containing the results of the query execution
        /// </returns>
        protected IList<T> QueryByValueObject<T>(IQuery queryObject, int firstResult, int maxResults, object valueObject)
        {
            this.PrepareQuery(queryObject);
            if (valueObject != null)
            {
                queryObject.SetProperties(valueObject);
            }

            if (firstResult >= 0)
            {
                queryObject.SetFirstResult(firstResult);
            }

            if (maxResults > 0)
            {
                queryObject.SetMaxResults(maxResults);
            }

            return queryObject.List<T>();
        }

        /// <summary>
        /// Execute a <see cref="IQuery"/>, binding paras to "?" parameters in the query
        /// string, limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care)
        /// </summary>
        /// <typeparam name="T">
        /// The specific return type
        /// </typeparam>
        /// <param name="detachedQueryObject">
        /// Hibernate query object
        /// </param>
        /// <param name="paras">
        /// The parameter values
        /// </param>
        /// <param name="firstResult">
        /// The index of the first result object to be retrieved (numbered from 0)
        /// </param>
        /// <param name="maxResults">
        /// The maximum number of result objects to retrieve (or &lt;=0 for no limit)
        /// </param>
        /// <returns>
        /// A containing the results of the query execution
        /// </returns>
        public IList<T> FindByQuery<T>(IDetachedQuery detachedQueryObject, int firstResult, int maxResults, object[] paras)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = detachedQueryObject.GetExecutableQuery(session);
                        return this.Query<T>(queryObject, firstResult, maxResults, paras);
                    });
        }

        /// <summary>
        /// Execute a <see cref="IQuery"/>,
        /// binding named parameters in the query string with properties of a value object, 
        /// limit the result list by firstResult and maxResult
        /// Cast the result to T parameter so the caller does not need to cast again
        /// (use with care)
        /// </summary>
        /// <typeparam name="T">
        /// The specific return type
        /// </typeparam>
        /// <param name="detachedQueryObject">
        /// Hibernate query object
        /// </param>
        /// <param name="valueObject">
        /// The parameter value object
        /// </param>
        /// <param name="firstResult">
        /// The index of the first result object to be retrieved (numbered from 0)
        /// </param>
        /// <param name="maxResults">
        /// The maximum number of result objects to retrieve (or &lt;=0 for no limit)
        /// </param>
        /// <returns>
        /// A containing the results of the query execution
        /// </returns>
        public IList<T> FindByQueryAndValueObject<T>(IDetachedQuery detachedQueryObject, int firstResult, int maxResults, object valueObject)
        {
            return this.Execute(
                session =>
                    {
                        var queryObject = detachedQueryObject.GetExecutableQuery(session);
                        return this.QueryByValueObject<T>(queryObject, firstResult, maxResults, valueObject);
                    });
        }
    }
}